package com.mbschenkel.server;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Pseudo-Singleton Wrapper for DB access 
 * (The Connection variable is a static and exists only once)
 * 
 * Drawback: DB Connection is never closed... :-/
 * 
 * @author mbs
 * 
 */
public class DBConnection {
	  // mbs: i copied mysql-connector-java-5.1.7-bin.jar to C:\Programme\Java\jre1.6.0_06\lib\ext
	  private static String driver = "com.mysql.jdbc.Driver";
	  private static String DbUrl = "jdbc:mysql://localhost:3306/latex" ;
	  // Change this to a file containing the password, stored at a secure location...
	  private static String pwdFileName = "C:\\Dokumente und Einstellungen\\mbs\\Desktop\\private_db_pwd.txt";
	  
	  private static Connection cn = null;
	  
	  public static Connection initializeConnection() {		  
		  try {
			  if( cn != null && cn.isValid(5) )
				  return cn; // reuse existing connection otherwise reconnect

			  // -- reconnect now from scratch -- 
			  
			  Class.forName( driver ).newInstance();
			  
			  File pwdFile = new File(pwdFileName);
			  FileReader fr = new FileReader(pwdFile);
			  BufferedReader bfr = new BufferedReader(fr);
			  String pwd = bfr.readLine();
			  
			  Connection cn = DriverManager.getConnection( DbUrl, "root", pwd );
			  return cn;
		  }	 catch( Exception ex ) {   
			  System.out.println( ex );
			  return null;
		  }
	  }
	  
	  public static void closeConnection() {
		  try{
			  if( ! cn.isClosed() )
				  cn.close();
		  } catch(Exception e) {}
	  }
	  
	  public static String querySingleValue( String query ) {
		  String result = "";
		  try { 
			  initializeConnection();

			  Statement  st = cn.createStatement();
			  ResultSet  rs = st.executeQuery( query );
			  //ResultSetMetaData rsmd = rs.getMetaData();
			  if( rs.first() ) {
				  result = rs.getString(0);
			  }
			  rs.close();
			  st.close();
		  } catch( SQLException ex ) {  
			  System.out.println( ex );
			  result = "";
		  }
		  return result;
	}
}
